﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Collections;
using System.Collections.ObjectModel;
using SYcwpb.Models;
using SYcwpb.Helpers;
using System.Data;

namespace SYcwpb.Access
{
    class TimeTableAccess
    {
        SqlDataLayer sqlHelper = new SqlDataLayer();
        string SQL_GetFileList = "select ID,TableName from TableInfo where LineID = '" + CONSTDATA.UserModel.Line + "'";

        string SQL_GetIDByName = "select ID from TableInfo where TableName = ";

        string SQL_DeleteFile = "delete from TableInfo where ID = ";
        string SQL_DeleteFileTable = "drop table ";
        string SQL_DeleteTrainDetailInfo = "delete from TrainDetailInfo where TableInfo_id = ";

        public ObservableCollection<TimeTableModel> GetFileList()
        {
            ObservableCollection<TimeTableModel> _fileList = new ObservableCollection<TimeTableModel>();
            DataTable dt = sqlHelper.SelectDt(SQL_GetFileList);
            if (null != dt && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                    _fileList.Add(TimeTableModel.CreateCSVFileModel(dt.Rows[i]["ID"].ToString(), dt.Rows[i]["TableName"].ToString()));

            }
            return _fileList;
        }

        public bool DeleteFile(TimeTableModel deleteModel)
        {
            string sql_DeleteFile = SQL_DeleteFile + deleteModel.ID;
            string sql_DeleteFileTable = SQL_DeleteFileTable + "[" + deleteModel.Name + "]";
            string sql_DeleteTrainDetailInfo = SQL_DeleteTrainDetailInfo + "'" + deleteModel.ID + "'";
            ArrayList sqlList = new ArrayList() { sql_DeleteFile, sql_DeleteFileTable, sql_DeleteTrainDetailInfo };

            return sqlHelper.ExecuteSqlTran(sqlList);
        }

        public string GetIdByName(string fileName)
        {
            string sql_GetIdByName = SQL_GetIDByName + "'" + fileName + "'";
            object result = sqlHelper.GetSingle(sql_GetIdByName);

            return result == null ? null : result.ToString();
        }

        public DataTable GetTableBetween(string start,string end,int line)
        {
            string sql_get = "select " +
                            " TIF.ID as ID," +
                            " TIF.TableName as Name," +
                            " AssignDate as Date" +
                            " from TableToDate TTD left join TableInfo TIF on TableInfo_id = TIF.ID" +
                            " where AssignDate between '" + start + "' and '" + end + "' and LineID = " + line.ToString() ;


            return sqlHelper.SelectDt(sql_get);
        }

        public string GetTodayTimeTable()
        {
            string sql_get = "select TableName from TableToDate TTD left join TableInfo TIF on TTD.TableInfo_id = TIF.ID "
                            + "where TTD.AssignDate = '" + DateTime.Today.ToString("yyyy-MM-dd") + "' and TIF.LineID = '" + CONSTDATA.UserModel.Line +  "'";

            object result = sqlHelper.GetSingle(sql_get);

            return null == result ? null : Convert.ToString(result);
        }
    }
}
